package homework2;

import JDBCLEARN.JdbcLearn;
import JDBCLEARN.JdbcLearnBackup;
import org.junit.Test;
import org.junit.experimental.runners.Enclosed;
import org.junit.runner.RunWith;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

import static java.lang.Class.forName;

@RunWith(Enclosed.class)
public class HomeWork {
    static Connection connection=null;
    public static class JdbcLearnBackup {
        public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
            JdbcLearn jdbcLearn = new JdbcLearn();
            jdbcLearn.insert();
        }


        @Test
        public void insert() throws SQLException, ClassNotFoundException {
            //1.加载驱动
            forName("com.mysql.cj.jdbc.Driver");

            //2.获取链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDb?useSSL=false&serverTimezone=UTC",
                    "root",
                    "123456");
            //3.定义sql

            String sql = "insert into student values(null,'李同学999','女',18,'csgo')";

            //4.创建数据操作对象
            Statement statement = connection.createStatement();

            //5.执行sql，获取结果集
            int i = statement.executeUpdate(sql);
            System.out.println("向数据库中插入了i = " + i);

            //6. 关闭资源
            //关闭资源
            statement.close();
            connection.close();
        }

        /**
         * 数据的查询
         *
         * @throws ClassNotFoundException
         * @throws SQLException
         */
        @Test
        public void selectData() throws ClassNotFoundException, SQLException {
            //1.加载驱动
            forName("com.mysql.cj.jdbc.Driver");
            //2.获取链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDb?useSSL=false&serverTimezone=UTC",
                    "root",
                    "123456");
//        System.out.println("connection = " + connection);

            //3.实现Crud
            String sql = "select * from student";
            //4.创建数据操作对象
            PreparedStatement statement = connection.prepareStatement(sql);
            //5.执行sql，获取结果集
            ResultSet resultSet = statement.executeQuery();

            //6.通过next指向结果集第一行
            while (resultSet.next()) {
                //7.解析结果中数据字段
                System.out.println("resultSet.getInt(\"sid\") = " + resultSet.getInt("sid"));
                System.out.println("resultSet.getString(\"ssex\") = " + resultSet.getString("ssex"));
                System.out.println("resultSet.getInt(\"sage\") = " + resultSet.getString("sage"));
                System.out.println("--------------------------------");
            }
            //关闭资源
            statement.close();
            connection.close();

        }

        /**
         * 删除某条记录
         *
         * @throws SQLException
         * @throws ClassNotFoundException
         */

        @Test
        public void deleteDb() throws SQLException, ClassNotFoundException {
            //1.加载驱动
            forName("com.mysql.cj.jdbc.Driver");

            //2.获取链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDb?useSSL=false&serverTimezone=UTC",
                    "root",
                    "123456");
            //3.定义sql


//拼接
            String sql = "delete from student where sname=?";

            System.out.println("sql = " + sql);


            //4.创建数据操作对象
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, "李同学999");

            //5.执行sql，获取结果集
            int i = statement.executeUpdate();
            System.out.println("删除数据库中 " + i);

            //6. 关闭资源
            //关闭资源
            statement.close();
            connection.close();
        }

        @Test
        public void inserttest() throws SQLException, ClassNotFoundException {
            //1.加载驱动
            forName("com.mysql.cj.jdbc.Driver");

            //2.获取链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDb?useSSL=false&serverTimezone=UTC",
                    "root",
                    "123456");
            //3.定义sql

            String sql = "insert into student values(null,?,?,?,?)";

            //4.创建数据操作对象
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, "博人");
            statement.setString(2, String.valueOf('男'));
            statement.setInt(3, 18);
            statement.setString(4, "cui");

            //5.执行sql，获取结果集
            int i = statement.executeUpdate();
            System.out.println("向数据库中插入了i = " + i);

            //6. 关闭资源
            //关闭资源
            statement.close();
            connection.close();
        }
    }
}
